USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TF' AND name = 'fn_split_xt') BEGIN
	DROP  FUNCTION  tsql.fn_split_xt
	PRINT '<<< DROP FUNCTION tsql.fn_split_xt - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*================================================================================================
Name:			tsql.fn_split_xt
Type:			User defined function 
Owner:					
Description:	Visual basic SPLIT function implementation for TSql, with additional functionality
				for extracting specified item, use of text qualifier, and multichar delimiter 
Parameters:		@list = list of values to be split
				@delimiter = delimiter used to sepparate list items
				@qualifier = text qualifier
				@item_no = List item to retrieve, use 0 for all
Dependens on:  	none
Usage:			SELECT * FROM [Lib].[tsql].[fn_split_xt] ('1;2;3;4;5',';',NULL,0)
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_split_xt&referringTitle=Documentation		
Version:		20121026
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		02/02/2007 - Adrian E Dudau - Function created
History:		06/19/2008 - Adrian E Dudau - Added qualifier and field no
History:		06/06/2009 - Adrian E Dudau - Implemented multichar delimiter
History:		01/04/2010 - Adrian E Dudau - Added ID column
History:		03/19/2010 - Adrian E Dudau - Added ItemNo column, extended @List to NVARCHAR(MAX), 
											  improved performance, simplified code.
History:		03/22/2010 - Adrian E Dudau - Added logic for NULL arguments 
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
History:		10/26/2012 - Adrian E Dudau - Improved performance, redesigned to use XML features instead 
History:		11/05/2012 - Adrian E Dudau - Added no-delimiter split capabilities  
================================================================================================*/

CREATE FUNCTION [tsql].[fn_split_xt] 
	( @list NVARCHAR(MAX)
	, @delimiter NVARCHAR(255) = '' 
	, @qualifier NVARCHAR(255) = ''
	, @item_no BIGINT = 0
	)
RETURNS @Result TABLE (id BIGINT IDENTITY(1,1),item_no BIGINT,item_value NVARCHAR(4000)) AS

   BEGIN /*FUNCTION*/
		DECLARE @XML XML 
		DECLARE @i INT = 0
		DECLARE @buffer AS NVARCHAR(MAX) = ''; 

		-- if the list is empty exit
		IF LEN(ISNULL(@list,'')) = 0 
			RETURN

		-- Collation added to help remove CHAR(0) 
		WHILE @i <= 31 BEGIN
				SET @list = REPLACE(@list COLLATE Latin1_General_BIN,CHAR(@i),'') 
				SET @i = @i + 1
		END	/*WHILE*/
		
		SET @list = REPLACE(@list,'>','~GT~')
		SET @list = REPLACE(@list,'<','~LT~')
		SET @list = REPLACE(@list,'&','~AMPSND~')

		-- Remove text qualifier 
		SET @list = REPLACE(@list,@qualifier,'')
	
		IF LEN(ISNULL(@delimiter,'')) = 0 AND @item_no < 0 BEGIN
			SET @i = 1
			
			WHILE @i <= LEN(ISNULL(@list,'')) BEGIN 
				SET @buffer = @buffer + SUBSTRING(@list,@i,ABS(@item_no)) + '</t><t>'
				
				SET @i = @i + ABS(@item_no) 
			END
			
			SET @XML = '<t>' + @buffer + '</t>'
			SET @item_no = 0 /*reset value so all valies are returned*/ 
		END
		ELSE BEGIN  
			SET @XML = '<t>' + REPLACE(@list, @delimiter, '</t><t>') + '</t>'
		END

		;WITH cteXML AS 
			(
			SELECT 'item_value' = REPLACE(
								  REPLACE(
								  REPLACE(
											x.i.value('.', 'VARCHAR(MAX)')
								,'~AMPSND~','&')
								,'~GT~','>')
								,'~LT~','<') 
					, 'sort_col' = 1			
			FROM @XML.nodes('//t') x(i)
			WHERE x.i.value('.', 'VARCHAR(MAX)') <> ''		
			)
		, cteResult AS 
			(
			SELECT 'item_no' = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY sort_col)  
				 , item_value 
			FROM cteXML 
			)
		/*END WITH*/ 
		
		INSERT INTO @Result
		SELECT item_no  
			 , item_value 
		FROM cteResult 
		WHERE item_no = ISNULL(NULLIF(@item_no,0),item_no) 
		
	RETURN 
END /*FUNCTION*/

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------

IF EXISTS(SELECT * FROM sysobjects WHERE type = 'TF' AND name = 'fn_split_xt') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_split_xt - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_split_xt - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

-- GRANT SELECT TO PUBLIC will be used at Db level



